﻿using Dapper;
using Domain.Entity.Basic_information;
using Domain.IRepository.Basic_information;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.DependencyInjection;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Volo.Abp.DependencyInjection;

namespace EFCore.Repository.Basic_information
{
    [Dependency(ServiceLifetime.Scoped)]
    public class DriversRepository : IDriversRepository
    {

        private MyDbContext myDbContext { get; set; }

        public DriversRepository(MyDbContext myDbContext)
        {
            this.myDbContext = myDbContext;
        }

        public List<Drivers_ment> GetDrivers(string date1, string date2, string value2)
        {
            string sql = "SELECT * FROM dbo.Drivers_ment WHERE Is_del=0";
            if(date1 != null && date2 != null)
            {
                sql += $"AND (Dv_dl_expiry>='{date1}' AND Dv_dl_expiry<='{date2}') ";
            }
            if(value2 != null && value2 != "")
            {
                sql += $"AND (Dv_name LIKE '%{value2}%')";
            }
            return myDbContext.Database.GetDbConnection().Query<Drivers_ment>(sql).ToList();
        }

        public int GetCount(string date1, string date2, string value2)
        {
            string sql = "SELECT * FROM dbo.Drivers_ment WHERE Is_del=0";
            if (date1 != null && date2 != null)
            {
                sql += $"AND (Dv_dl_expiry>='{date1}' AND Dv_dl_expiry<='{date2}') ";
            }
            if (value2 != null && value2 != "")
            {
                sql += $"AND (Dv_name LIKE '%{value2}%')";
            }
            return myDbContext.Database.GetDbConnection().Query<Drivers_ment>(sql).Count();
        }

        public int AddDrivers(Drivers_ment drivers)
        {
            string sql = $@"INSERT INTO dbo.Drivers_ment(Dv_name, Dv_phone, Dv_identy, Dv_gender, Dv_dl_type, Dv_dl_expiry, Dv_carrier, Dv_address, Dv_emg_cont, Dv_emg_ph, Dv_drvr_st, Dv_doc_pic, Dv_drive_lic, Dv_cmts, Create_by, Create_time, Is_del)
                        VALUES('{drivers.Dv_name}','{drivers.Dv_phone}','{drivers.Dv_identy}','{drivers.Dv_gender}','{drivers.Dv_dl_type}','{drivers.Dv_dl_expiry}','{drivers.Dv_carrier}','{drivers.Dv_address}','{drivers.Dv_emg_cont}','{drivers.Dv_emg_ph}','{drivers.Dv_drvr_st}','{drivers.Dv_doc_pic}','{drivers.Dv_drive_lic}','{drivers.Dv_cmts}','{drivers.Create_by}','{drivers.Create_time}','{drivers.Is_del}')";
            return myDbContext.Database.GetDbConnection().Execute(sql);
        }

        public Drivers_ment GetDriver(int id)
        {
            string sql = $"SELECT * FROM dbo.Drivers_ment WHERE Is_del=0 AND Dv_id={id}";
            return myDbContext.Database.GetDbConnection().Query<Drivers_ment>(sql).FirstOrDefault();
        }

        public int UpdDrivers(Drivers_ment driver)
        {
            string sql = $@"UPDATE dbo.Drivers_ment SET Dv_name='{driver.Dv_name}', Dv_phone='{driver.Dv_phone}', Dv_identy='{driver.Dv_identy}', Dv_gender='{driver.Dv_gender}', Dv_dl_type='{driver.Dv_dl_type}', Dv_dl_expiry='{driver.Dv_dl_expiry}', Dv_carrier='{driver.Dv_carrier}', Dv_address='{driver.Dv_address}', Dv_emg_cont='{driver.Dv_emg_cont}', Dv_emg_ph='{driver.Dv_emg_ph}', Dv_drvr_st='{driver.Dv_drvr_st}', Dv_doc_pic='{driver.Dv_doc_pic}', Dv_drive_lic='{driver.Dv_drive_lic}', Dv_cmts='{driver.Dv_cmts}', Update_by='{driver.Update_by}', Update_time='{driver.Update_time}'
                        WHERE Is_del=0 AND Dv_id={driver.Dv_id}";
            return myDbContext.Database.GetDbConnection().Execute(sql);
        }

        public int DelDriver(int id)
        {
            string sql = $"UPDATE dbo.Drivers_ment SET Is_del=1 WHERE Dv_id={id}";
            string sql2 = $"UPDATE dbo.Vehicles_ment SET Is_del=1 WHERE Dv_id={id}";
            int flag = myDbContext.Database.GetDbConnection().Execute(sql2);
            return myDbContext.Database.GetDbConnection().Execute(sql);
        }

        public Drivers_ment GetById(int id)
        {
            string sql = $"SELECT Dv_name FROM dbo.Drivers_ment WHERE Is_del=0 AND Dv_id={id}";
            return myDbContext.Database.GetDbConnection().Query<Drivers_ment>(sql).FirstOrDefault();
        }
    }
}
